﻿using Domain.Entity.zct;
using Domain.Entity;
using Domain.IRepository;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Dapper;
using Domain.Entity.bs;
using System.Data;

namespace EFCore.Repository
{
    public class PositionRepository:IPositionRepository
    {
        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";
        //全部查
        public async Task<List<Position>> GetAll()
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 构建查询条件
                string sql = $"SELECT * FROM zct_position WHERE is_delete=0";
                return (await sqlConnection.QueryAsync<Position>(sql)).AsList();
            }
        }

        //条件查
        public async Task<FenYe<Position>> GetCondition(FenYe<Position> fenYe, string? Position_code, string? Position_name, int? Position_status)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 构建查询条件
                string whereBuilder = $" is_delete=0 ";
                var parameters = new DynamicParameters();

                if (!string.IsNullOrEmpty(Position_code))
                {
                    whereBuilder += $" and (Position_code like '%{Position_code}%' or Position_name like '%{Position_name}%') ";
                }
                if (Position_status.HasValue)
                {
                    whereBuilder += $" and Position_status = {Position_status}";
                }

                // 分页参数
                parameters.Add("@pageSize", 10);
                parameters.Add("@keyName", "position_sort"); // 主键字段
                parameters.Add("@tabelName", "zct_position");
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@where", whereBuilder);
                parameters.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);

                // 执行存储过程
                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                var result = await sqlConnection.QueryAsync<Position>(sql, parameters);

                // 设置分页结果
                fenYe.List = result.ToList();
                fenYe.Rows = parameters.Get<int>("@rows");
                return fenYe;
            }
        }

        //id查询
        public async Task<Position> GetID(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = "select position_id,position_code,position_name,position_status,position_sort,is_delete,remark from [dbo].[zct_position] where is_delete=0 AND position_id = @id";

                var parameters = new DynamicParameters();
                parameters.Add("@id", id);

                return await sqlConnection.QueryFirstOrDefaultAsync<Position>(sql, parameters);
            }
        }

        //停用
        public async Task<int> GetDeactivated(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[zct_position] set Position_status=1 where position_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //启用
        public async Task<int> GetEnable(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[zct_position] set Position_status=0 where position_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //编辑
        public async Task<int> GetEdit(Position position)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                var sql = @"
                UPDATE zct_position
                SET 
                    Position_status = @Position_status, 
                    Position_name = @Position_name, 
                    Position_sort = @Position_sort, 
                    Remark = @Remark
                WHERE Position_id = @Position_id;";

                var parameters = new DynamicParameters();
                parameters.Add("@Position_status", position.Position_status);
                parameters.Add("@Position_name", position.Position_name);
                parameters.Add("@Position_sort", position.Position_sort);
                parameters.Add("@Remark", position.Remark);
                parameters.Add("@Position_id", position.Position_id);

                return await sqlConnection.ExecuteAsync(sql, parameters);
            }
        }

        //添加
        public async Task<int> GetAdd(Position position)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                var sql = @"
                INSERT INTO zct_position (
                    Position_code, Position_name, Position_status, Position_sort, Remark
                ) VALUES (
                    @Position_code, @Position_name, @Position_status, @Position_sort, @Remark
                );";

                var parameters = new DynamicParameters();
                parameters.Add("@Position_code", position.Position_code);
                parameters.Add("@Position_name", position.Position_name);
                parameters.Add("@Position_status", position.Position_status);
                parameters.Add("@Position_sort", position.Position_sort);
                parameters.Add("@Remark", position.Remark);

                return await sqlConnection.ExecuteAsync(sql, parameters);
            }
        }

        //删除
        public async Task<int> GetDelete(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 1. 检查岗位是否存在且未被删除
                var checkExistsSql = "SELECT COUNT(1) FROM zct_position WHERE position_id = @id AND is_delete = 0";
                var exists = await sqlConnection.QuerySingleAsync<int>(checkExistsSql, new { id });
                if (exists == 0)
                {
                    return -1; // 岗位不存在或已被逻辑删除
                }

                // 2. 检查是否有未删除的引用记录
                // 注意：根据你的表结构，字段名是 position_id
                var checkSql = @"
                -- 检查销售退货单
                SELECT COUNT(*) FROM zct_user WHERE position_id = @id AND is_delete = 0";

                var counts = await sqlConnection.QueryAsync<int>(checkSql, new { id });

                // 如果任何一个表有引用记录，都不能删除
                if (counts.Any(count => count > 0))
                {
                    return 0; // 有引用，删除失败
                }

                // 3. 没有引用，执行逻辑删除
                var updateSql = @"
                UPDATE zct_position
                SET is_delete = 1
                WHERE position_id = @id AND is_delete = 0"; // 再次确认未被删除，防止并发问题

                var rowsAffected = await sqlConnection.ExecuteAsync(updateSql, new { id });
                // 理论上 rowsAffected 应该是 1，但如果并发情况下已被其他请求删除，则可能是 0
                return rowsAffected > 0 ? 1 : -1;
            }
        }
    }
}
